package com.mzj.saas.jobs.report.service;

import com.mzj.saas.jobs.EnterpriseData.entity.DepartmentHierarchy;
import com.mzj.saas.jobs.EnterpriseData.repository.DepartmentHierarchyRepository;
import com.mzj.saas.jobs.commons.DateUtils;
import com.mzj.saas.jobs.report.entity.DepartmentEnterpriseTagRef;
import com.mzj.saas.jobs.report.repository.DepartmentEnterpriseTagRefRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 缴费统计
 */

@Service
public class ReportPaymentStatisticsService {

    private static Logger log = LoggerFactory.getLogger(ReportPaymentStatisticsService.class);

    private static Map<String, Object> dateMap = new HashMap<>();

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private DepartmentHierarchyRepository departmentHierarchyRepository;
    @Autowired
    private DepartmentEnterpriseTagRefRepository departmentEnterpriseTagRefRepository;

    public String work() throws Exception {

        List<DepartmentHierarchy> departmentHierarchyList = departmentHierarchyRepository.findAll();
        //物业公司集合
        List<DepartmentHierarchy> propertyList = new ArrayList<>();
        for (DepartmentHierarchy dh : departmentHierarchyList) {
            List<DepartmentEnterpriseTagRef> entityList = departmentEnterpriseTagRefRepository.findByDepRefId(dh.getId());
            List<Long> tagIdList = entityList.stream().map(DepartmentEnterpriseTagRef::getTagId).collect(Collectors.toList());
            if (tagIdList.contains(4L)) {    //物业公司
                propertyList.add(dh);
            }
        }

        Date date = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
        String lDate = formatter.format(date);

        List<String> datas = new ArrayList<>();

        SimpleDateFormat df = new SimpleDateFormat("HH");
        String str = df.format(date);
        int hh = Integer.parseInt(str);
        if (hh >= 0 && hh <= 6) {
            //判断到凌晨只统计一次昨天数据
            Date day = DateUtils.getYesterday(new Date());
            String yesterday = formatter.format(day);
            if (!dateMap.containsKey(yesterday)) {
                dateMap.clear();
                dateMap.put(yesterday, yesterday);
                datas.add(yesterday);
            }
        }
        datas.add(lDate);


        for (String localDate : datas) {
            log.info("ReportPaymentStatisticsService 缴费统计  start  date={}", localDate);

            String deleteMxSQL = " delete from saas_report_payment_statistics where stat_date=  " + localDate;
            //删除当前统计时间记录
            jdbcTemplate.execute(deleteMxSQL);

            for (DepartmentHierarchy hierarchy : propertyList) {

                //查询 租金/押金账单明细 saas_rent_bill  当天已缴费记录
                String rSQL = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_rent_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + " AND b.payment_status=1 AND DATE_FORMAT(b.payment_time,'%Y%m%d')= " + localDate;

                Map<String, Object> rMap = jdbcTemplate.queryForMap(rSQL);

                //查询  其它费用账单明细 saas_expense_bill 当天已缴费记录
                String eSQL = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_expense_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + " AND b.payment_status=1 AND DATE_FORMAT(b.payment_time,'%Y%m%d')= " + localDate;

                Map<String, Object> eMap = jdbcTemplate.queryForMap(eSQL);

                Integer rAlreadyPayAmount = Integer.parseInt(rMap.get("total_cost").toString()) + Integer.parseInt(eMap.get("total_cost").toString());


                //查询 物业房产-租金/押金账单明细 saas_estate_rent_bill  当天已缴费记录
                String rentBillSQL = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_estate_rent_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + " AND b.payment_status=1 AND DATE_FORMAT(b.payment_time,'%Y%m%d')= " + localDate;

                Map<String, Object> rentBillMap = jdbcTemplate.queryForMap(rentBillSQL);

                //查询 物业房产-其它费用账单明细 saas_estate_expense_bill 当天已缴费记录
                String expenseBillSQL = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_estate_expense_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + " AND b.payment_status=1 AND DATE_FORMAT(b.payment_time,'%Y%m%d')= " + localDate;

                Map<String, Object> expenseBillMap = jdbcTemplate.queryForMap(expenseBillSQL);

                //查询 报修费用 当天生成记录
                String repairsSQL = " SELECT IFNULL(SUM(cd.total_cost),0) total_cost FROM saas_estate_repairs_record rr LEFT JOIN saas_estate_repairs_cost_detail cd ON rr.id = cd.repairs_id LEFT JOIN saas_repairs_payment_order po ON rr.id = po.repairs_id " +
                        " INNER JOIN saas_house h ON rr.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + " AND rr.status = 4 AND DATE_FORMAT(rr.payment_time,'%Y%m%d')= " + localDate;

                Map<String, Object> repairsMap = jdbcTemplate.queryForMap(repairsSQL);

                Integer repairs = Integer.parseInt(repairsMap.get("total_cost").toString());

                //已缴费金额
                Integer alreadyPayAmount = Integer.parseInt(rentBillMap.get("total_cost").toString()) + Integer.parseInt(expenseBillMap.get("total_cost").toString()) + rAlreadyPayAmount + repairs;


                //查询 租金/押金账单明细 saas_rent_bill  当天已正常缴费记录
                String nSQL1 = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_rent_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + "  AND b.payment_status=1 AND DATE_FORMAT(b.payment_time,'%Y%m%d')= " + localDate +
                        " AND b.payment_time BETWEEN b.start_time AND b.end_time  ";

                Map<String, Object> nMap1 = jdbcTemplate.queryForMap(nSQL1);

                //查询 其它费用账单明细 saas_expense_bill 当天已正常缴费记录
                String nSQL2 = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_expense_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + "  AND b.payment_status=1 AND DATE_FORMAT(b.payment_time,'%Y%m%d')= " + localDate +
                        " AND b.payment_time BETWEEN b.start_time AND b.end_time  ";

                Map<String, Object> nMap2 = jdbcTemplate.queryForMap(nSQL2);

                Integer nPaymentAmount = Integer.parseInt(nMap1.get("total_cost").toString()) + Integer.parseInt(nMap2.get("total_cost").toString());

                //查询 物业房产-租金/押金账单明细 saas_estate_rent_bill  当天已正常缴费记录
                String normalSQL1 = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_estate_rent_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + "  AND b.payment_status=1 AND DATE_FORMAT(b.payment_time,'%Y%m%d')= " + localDate +
                        " AND b.payment_time BETWEEN b.start_time AND b.end_time  ";

                Map<String, Object> normalMap1 = jdbcTemplate.queryForMap(normalSQL1);

                //查询 物业房产-其它费用账单明细 saas_estate_expense_bill 当天已正常缴费记录
                String normalSQL2 = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_estate_expense_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + "  AND b.payment_status=1 AND DATE_FORMAT(b.payment_time,'%Y%m%d')= " + localDate +
                        " AND b.payment_time BETWEEN b.start_time AND b.end_time  ";

                Map<String, Object> normalMap2 = jdbcTemplate.queryForMap(normalSQL2);

                //正常缴费金额
                Integer normalPaymentAmount = Integer.parseInt(normalMap1.get("total_cost").toString()) + Integer.parseInt(normalMap2.get("total_cost").toString()) + nPaymentAmount;


                //查询 租金/押金账单明细 saas_rent_bill  账单结束当天未缴费记录
                String oSQL1 = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_rent_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + "  AND b.payment_status=0 AND DATE_FORMAT(b.end_time,'%Y%m%d')= " + localDate;

                Map<String, Object> oMap1 = jdbcTemplate.queryForMap(oSQL1);

                //查询 其它费用账单明细 saas_expense_bill 账单结束当天未缴费记录
                String oSQL2 = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_expense_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + "  AND b.payment_status=0 AND DATE_FORMAT(b.end_time,'%Y%m%d')= " + localDate;

                Map<String, Object> oMap2 = jdbcTemplate.queryForMap(oSQL2);

                Integer oPaymentAmount = Integer.parseInt(oMap1.get("total_cost").toString()) + Integer.parseInt(oMap2.get("total_cost").toString());

                //查询 物业房产-租金/押金账单明细 saas_estate_rent_bill  账单结束当天未缴费记录
                String overSQL1 = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_estate_rent_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + "  AND b.payment_status=0 AND DATE_FORMAT(b.end_time,'%Y%m%d')= " + localDate;

                Map<String, Object> overMap1 = jdbcTemplate.queryForMap(overSQL1);

                //查询 物业房产-其它费用账单明细 saas_estate_expense_bill 账单结束当天未缴费记录
                String overSQL2 = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_estate_expense_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + "  AND b.payment_status=0 AND DATE_FORMAT(b.end_time,'%Y%m%d')= " + localDate;

                Map<String, Object> overMap2 = jdbcTemplate.queryForMap(overSQL2);

                //逾期未缴费金额
                Integer overduePaymentAmount = Integer.parseInt(overMap1.get("total_cost").toString()) + Integer.parseInt(overMap2.get("total_cost").toString()) + oPaymentAmount;


                //查询 租金/押金账单明细 saas_rent_bill  账单结束当天已缴费记录
                String oaSQL1 = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_rent_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + "  AND b.payment_status = 1 AND DATE_FORMAT(b.end_time,'%Y%m%d')= " + localDate;

                Map<String, Object> oaMap1 = jdbcTemplate.queryForMap(oaSQL1);

                //查询 其它费用账单明细 saas_expense_bill 账单结束当天已缴费记录
                String oaSQL2 = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_expense_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + "  AND b.payment_status=1 AND DATE_FORMAT(b.end_time,'%Y%m%d')= " + localDate;

                Map<String, Object> oaMap2 = jdbcTemplate.queryForMap(oaSQL2);

                Integer oaPaymentAmount = Integer.parseInt(oaMap1.get("total_cost").toString()) + Integer.parseInt(oaMap2.get("total_cost").toString());

                //查询 物业房产-租金/押金账单明细 saas_estate_rent_bill  账单结束当天已缴费记录
                String overaSQL1 = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_estate_rent_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + "  AND b.payment_status=1 AND DATE_FORMAT(b.end_time,'%Y%m%d')= " + localDate;

                Map<String, Object> overaMap1 = jdbcTemplate.queryForMap(overaSQL1);

                //查询 物业房产-其它费用账单明细 saas_estate_expense_bill 账单结束当天已缴费记录
                String overaSQL2 = " SELECT IFNULL(SUM(b.cost),0) total_cost FROM saas_estate_expense_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + "  AND b.payment_status=1 AND DATE_FORMAT(b.end_time,'%Y%m%d')= " + localDate;

                Map<String, Object> overaMap2 = jdbcTemplate.queryForMap(overaSQL2);

                //逾期已缴费金额
                Integer overdueAlreadyAmount = Integer.parseInt(overaMap1.get("total_cost").toString()) + Integer.parseInt(overaMap2.get("total_cost").toString()) + oaPaymentAmount;


                //数据插入  saas_report_payment_statistics
                String insertSql = " INSERT INTO saas_report_payment_statistics(stat_date,month_date,already_pay_amount,normal_payment_amount,overdue_payment_amount,overdue_already_amount,house_property_id)  " +
                        " VALUE(?,?,?,?,?,?,?) ";

                //月份
                String month = localDate.substring(0, 6);

                jdbcTemplate.update(insertSql, ps -> {
                    ps.setString(1, localDate);
                    ps.setString(2, month);

                    ps.setInt(3, alreadyPayAmount);
                    ps.setInt(4, normalPaymentAmount);
                    ps.setInt(5, overduePaymentAmount);
                    ps.setInt(6, overdueAlreadyAmount);
                    ps.setLong(7, hierarchy.getOrgId());
                });

            }
            log.info("ReportPaymentStatisticsService 缴费统计  end");
        }

        return "SUCCESS";
    }


}
